This invention relates to the field of transaction logging in the storage of data in database management systems (DBMS) such as relational database management systems.
For reliable operation of a database management system, integrity and consistency are needed for the storage and updating of data for the database management system. To facilitate this requirement, one of the key features of a mature database management system is transaction logging. For each change to the database content, there is one or more log record that describes this change. Each log record is assigned a specific sequence number and log records are eventually transferred to permanent storage in same order as this sequence number. These log records must eventually be placed on permanent storage in order to ensure consistency and to provide a history of changes to the database. For relational database management systems (RDBMS) such as DB2, transaction logging is an integral process involved in making changes to the data contents of the database tables stored by the RDBMS. These changes include inserting, deleting or updating the database contents. For mature database management systems, transaction log records are first logged by database connection agents in a transaction log buffer in memory before being transferred to permanent storage such as a disk. The database manager generally has a dedicated thread of execution called the logger to manage the transferring of data from log buffer to permanent storage. The purpose of the transaction log buffer is to reduce the cost of small changes to the database contents by making the cost of logging of these small changes relatively inexpensive. Placing transaction log records into a log buffer is relatively simple when a single database application is involved since there is no contention for the use of the transaction log buffer by other application. The speed at which transaction logging takes place is dependent on the amount of data that the single application provides for transaction logging and the frequency that the application makes changes to the database contents. Transaction logging for a single database application can be handled in a simple serial fashion and there is no contention on the transaction log buffer. However; the situation becomes more complex for a database management system which can serve multiple database connections simultaneously. An efficient transaction logging mechanism is required that can handle the logging of data by multiple database applications to multiple database tables on permanent storage. With multiple database applications, contention for the transaction log buffer can inhibit transaction logging performance due to the serialized access to the transaction log buffer. For workloads which involve a high frequency of transactions that make changes to the database contents, transaction logging can become a performance bottleneck.
In prior art, changes made to the database by multiple database applications making use of the RDBMS involved granting exclusive access to the log buffer to one database connection agent (a database connection agent in this context is a task or process that resides in the RDBMS engine and performs various operations on behalf of a database application). The access to the log buffer is sequential, requiring other database connection agents to wait until the current database application""s data had been copied into the log buffer. This serialized access can have a large negative impact to transaction logging throughput since only one database connection agent can copy transaction log records at any given time.
The following generally summarizes the steps taken in prior art when a transaction log record was copied into the transaction log buffer:
1. Obtain a latch (a latch in this context is simply a high speed mutually exclusive locking mechanism) which protects the transaction log buffer from having more than one database connection agent copying a transaction log record into it. Once the transaction log buffer is latched by one database connection agent, other database connection agents must wait until the latch is freed and the latch is obtained by the database connection agent.
2. Copy the transaction log record into the transaction log buffer starting at the current log buffer offset.
3. Update the transaction log buffer offset which marks the location that the next transaction log record should be copied to.
4. Release the latch which protects the transaction log buffer so that other database database connection agents can attempt to obtain the latch.
One problem in the prior art is that there is too much serialization in accessing the log buffer. It appears that there is no recognition in the prior art that the latch is only needed to determine the sequential order of the log records and the location in the log buffer for loading log record data. We have found that it is not necessary (at least for the purpose of determining the sequential order of log records) to hold the latch while copying log record data into the log buffer. If database connection agents are allowed to copy their log record data after releasing the latch (so multiple database connection agents can be copying concurrently), a problem is not knowing when database connection agents will have finished copying data into the log buffer.
We have found that the logger, which is used to transfer data records from the transaction log buffer to main storage needs to know when it can start transferring data from the log buffer to permanent storage. One aspect of this invention addresses this.
The prior art also had to contend with two competing goals when trying to improve transaction logging performance: one being application response time and the second being overall transaction logging throughput. This issue is related to the transferring of data from the log buffer to permanent storage. The performance of a storage device such as a disk usually varies with the amount of data for each transfer, and generally increases with the transfer size up to a certain level, depending on system characteristics, after which the performance gain becomes smaller for increasing transfer size. A storage device has a transfer size (or a range of transfer sizes) at which data transferring becomes efficient and levels off with larger transfer sizes. However, during the operation of the logger, there may be different amounts of data available in the logger buffer ready to be transferred to permanent storage. If the logger is implemented with logic to initiate the data transfer for all data available in the log buffer, the size of the transfer may be very large resulting in poor response time for some of the applications making use of the RDBMS. It is also possible that the amount of data to be transferred may be too small for good efficiency, which can result in poor system throughput. Taken to the extreme, if the logger writes each transaction record to storage one at a time, then each change to the database would have to endure the cost of writing a log record to permanent storage. While writing a specific log record to storage immediately benefits the individual database connection agent which made the request, the overall throughput is hindered, as will be appreciated by those skilled in the art. On the other hand, the overall throughput can benefit from waiting for a number of transaction records to be copied into the log buffer and then transferring them all to permanent storage at the same time. The longer the transaction logging task waits, the worse the potential response time to an individual request and the shorter the transaction logging task waits, the worse the overall throughput. One aspect of this invention addresses this issue by providing a preselected data transfer amount for use by the logger in transferring data from the log buffer to permanent storage.
Another potential problem with prior art is that the transaction logging task (performed by the logger) was responsible for both writing transaction records to permanent storage and performing other work. The other work may involve responding to various requests (for example, reading some of the transaction records that have been previously written to the storage) from potentially thousands of database connection agents. While the transaction logging task is handling the transfer of transaction records to permanent storage, it is not able to perform other work and while the transaction logging task is performing other work, it is not able to transfer transaction records to permanent storage. The invention can increase the time available for the logging task to perform these other duties. The performance of these other duties is not the subject matter of this invention and will not be discussed further.
The invention herein seeks to overcome the disadvantages of the prior art by providing an improved method and apparatus for the logging of transaction data from multiple database applications to be transferred to permanent storage for a database management system.
As may be apparent herein there are a number of aspects to the invention herein; for instance: a method is provided for storing transaction data for a plurality of database applications in a multiple access database management system having permanent storage and a transaction log buffer to store data for the database applications before transferring the data to the permanent storage, wherein database connection agents associated with the applications are used to store data in the transaction log buffer, comprising:
granting exclusive write access reservations in the transaction log buffer to a plurality of the database connection agents; and,
allowing database connection agents to write transaction data records to previously granted write access reservations while granting exclusive access reservations to other database connection agents.
From another perspective the method includes:
granting an exclusive write access reservation in the transaction log buffer to a database connection agent;
granting an exclusive write access reservation in the transaction log buffer to another database connection agent; while allowing the previous database connection agent to write a data record to its respective write access reservation in the transaction log buffer.
Also, write access reservations may be granted sequentially in the transaction log buffer to database connection agents; while allowing database connection agents to write data records to previously granted write access reservations in the transaction log buffer.
The connection agents preferably write data records independently of and concurrently with other database connection agents to previously granted write access reservations in the transaction log buffer.
The transaction log buffer may be divided into a multiplicity of contiguous logical sections of a predetermined size; for the purpose of sequentially granting contiguous write access reservations in the logical sections of the transaction log buffer to database connection agents; while allowing other database connection agents to write data records concurrently to previously granted write access reservations in the transaction log buffer.
When the reservations fill logical section; the process may beneficially sequentially grant continguous write access reservations in a next logical section of the transaction log buffer.
The method may include determining if all reservations in a logical section have been filled with data records, copying transaction data records from a data filled logical section to the permanent storage; and, subsequently making the data filled logical section available for data storage.
From another aspect the method provides for storing transaction data for a multiplicity of database applications in a multiple access database management system having permanent storage, and a transaction log buffer divided into a multiplicity of logical sections of predetermined equal size, to temporarily store data for the database applications before transferring the data to the permanent storage, wherein database connection agents associated with the database applications are used to store data in the transaction log buffer, and a logger is used to copy data from the transaction log buffer to the permanent storage, comprising:
allowing database connection agents to write data records asynchronously to any previously granted write access reservations in the transaction log buffer;
sequentially granting contiguous write access reservations in a the logical section of the transaction log buffer to database connection agents until the reservations fill the logical section; and in turn sequentially granting continguous write access reservations in a next logical section of the transaction log buffer;
counting the number of reservations made in a logical section and counting the number of reservations in the logical section that have been filled with data records
comparing the number of reservations made in a logical section with the number of reservations filled with data in the logical section;
when the number of reservations made and the number of reservations filled are equal marking the logical section as a filled logical section;
allowing data records from the filled logical section to be copied to permanent storage; copying transaction data records from the data filled logical section to the permanent storage;
determining when all data records from the filled logical section have been copied to permanent storage;
making the data filled logical section available for data storage when all data records from the data filled logical section have been copied to permanent storage.
Advantageously, in the method the logger may be used to copy data records from the data filled logical section to the permanent storage; and, the data filled logical section will be marked available for data storage after the logger has completed copying data records from the data filled logical section.
From another aspect the method of storing transaction data for a multiplicity of database applications in a multiple access database management system having permanent storage and a transaction log buffer, having a buffer latch to ensure exclusive access for establishing reservations, the transaction log buffer being adapted to store data for the database applications before transferring the data to the permanent storage, wherein database connection agents associated with the database applications are used to store data in the transaction log buffer, includes:
granting control of the buffer latch at a first position in the transaction log buffer to a first database connection agent requesting write access to write a predetermined transaction data record to the transaction buffer;
determining the size of the data record;
updating the position of the log buffer offset while holding the buffer latch to a second position to define a first data reservation in the transaction log buffer between the first and second positions corresponding to the size of the data record;
granting exclusive write access to the first data reservation to the first database connection agent;
releasing the control of the latch from the first database connection agent;
granting control of the buffer latch at the second position in the transaction log buffer to a second database connection agent;
allowing the first database connection agent to copy the predetermined transaction data record into the first data reservation.
More specifically the method may include granting contiguous write access reservations in the transaction log buffer to database connection agents; while allowing database connection agents to write data records asynchronously to previously granted write access reservations in the transaction log buffer by:
a) granting control of the buffer latch at an initial position in the transaction log buffer to a database connection agent requesting write access to write a predetermined transaction data record to the transaction buffer to define a beginning position of a data reservation for writing;
b) determining the size of the data record of the database connection agent;
c) updating the position of the log buffer offset while holding the latch to an end position for writing to define i) a data reservation for the database applications in the transaction log buffer between the beginning and end positions corresponding to the size of the data record, and ii) an initial position for writing a next data reservation;
d) granting exclusive write access to the data reservation to the database connection agent;
releasing the control of the latch from the database connection agent;
e) granting control of the buffer latch to another database connection agent at the initial position for writing a next data reservation in the transaction log buffer;
f) repeating steps a to e, and
allowing any database connection agents to copy predetermined transaction data records into their respective data reservations even while control of the buffer latch is under control of any other database connection agent.
Another aspect of the invention provides data processing system for storing transaction data for a plurality of database applications in a multiple access database management system having permanent storage and a transaction log buffer to store data for the database applications before transferring the data to the permanent storage, wherein database connection agents associated with the applications are used to store data in the transaction log buffer, comprising:
means for Granting exclusive write access reservations in the transaction log buffer to a plurality of the database connection agents; and,
means for allowing database connection agents to write transaction data records to previously granted write access reservations while granting exclusive access reservations to other database connection agents.
In addition the invention advantageously provides software that may be incorporated in an article including:
a computer-readable signal bearing medium wherein said medium is a recordable data storage medium or a modulated carrier signal;
means in the medium for storing transaction data for a plurality of database applications in a multiple access database management system having permanent storage and a transaction log buffer to store data for said database applications before transferring said data to said permanent storage, wherein database connection agents associated with said applications are used to store data in said transaction log buffer, comprising:
means for granting exclusive write access reservations in said transaction log buffer to a plurality of said database connection agents; and,
means for allowing database connection agents to write transaction data records to previously granted write access reservations while granting exclusive access reservations to other database connection agents.
Another advantageous aspect of the invention provides a computer program product adaptable for embodiment on a computer readable signal-bearing medium comprising computer program code means adapted to perform the steps of the method of the invention when said program is run on a computer.
A number of terms are used in this application and are discussed herein:
Database connection agent A database connection agent is a thread or process that performs a task on behalf of a database application; it may run within the DBMS engine and may report when the task has been accomplished.
Log buffer (transaction log buffer) As contemplated herein a database management system has a transaction log buffer that is shared among various database connection agents of the database management system in order to store data in permanent storage of the database management system.
Log record (transaction log record) A log record is a collection of data that represents one or more changes to the data contents of a database management system.
Logger (transaction logging process or task) This is a special process or task in the dbms system which controls the copying of data records from the transaction log buffer to permanent storage, which is usually implemented as disk storage.
Log buffer offset This term marks the location in the log buffer at which a reservation will be started in preparation for the loading of a log record by a database connection agent. The value of the offset is related to the beginning of the log buffer.
Latch A latch in the context of this invention may be implemented as a high speed mutually exclusive locking mechanism that can be used to serialize access to a resource in shared memory or storage. The transaction log buffer has a latch, for which an database connection agent acquires control to prevent another database connection agent from obtaining write access to the buffer. If one database connection agent has control of the latch of the transaction log, another database connection agent would have to wait until the first database connection agent releases the latch until it could gain write access to the transaction log. The latch serializes access to the transaction log buffer. If one database connection agent acquires the latch, other database connection agents have to wait until the latch is released.
Chunk For the purposes of this invention the transaction log buffer may be divided into logical divisions which we will call xe2x80x98chunksxe2x80x99. In a preferred embodiment of the invention herein a counter is provided for each chunk that is used to track when the chunk has been filled.
Current Chunk For the purposes of this invention, the current chunk is the chunk which contains the log buffer offset.
Chunk Counter There is one chunk counter for each chunk in the log buffer. This is used to keep track of outstanding copies that need to be loaded into the log buffer.
Reservation A reservation is a portion of the log buffer which has been reserved for the exclusive use by one database connection agent for the purpose of loading data.
Under a preferred embodiment of the present invention, the log buffer of a database management system is divided into logical divisions called chunks. The size of a chunk is chosen to be large enough that writing its contents to permanent storage is reasonably efficient and small enough that if the logger needs to wait until a chunk is full before writing its contents to permanent storage, it won""t seriously impact database application response time.
In another aspect of this invention, an database connection agent working on behalf of a database application no longer copies transaction log records into the log buffer while holding the log buffer latch. Instead, the latch need only be held long enough to determine the location in the log buffer to which a log record is to be copied, and to update the log buffer offset to represent the location in the log buffer for the start of the next log record. To indicate that the log record data has not yet been copied into the log buffer, the chunk counter is also incremented. In one embodiment of the present invention the incrementing of the chunk counter is done while holding the latch. If the log record spans multiple chunks, the counter for each of the affected chunks is incremented. The log buffer latch is then released in one embodiment of the present invention. After releasing the latch, the database connection agent then copies its transaction log record data to the buffer location (reservation) that was determined when the latch was held. The chunk counter is decremented after the data is copied into the log buffer. Again, if the log record spans multiple chunks, the counter for each of the pertinent chunks is decremented. The log buffer latch does not need to be held for the decrementing of the chunk counter.
Under the preferred embodiment of the invention, the logger is still responsible for writing the contents of the log buffer to permanent storage. The chunk counter allows the logger to determine that a chunk (ie. a section of the log buffer) is filled with log record data. If the chunk counter is greater than zero, there are still outstanding log records to be copied into the chunk. If the chunk counter is zero, there are no outstanding log records to be copied to the chunk. Once the chunk has been filled, the logger is then free to write the contents of the chunk asynchronously to permanent storage. If multiple chunks are filled, the logger will issue asynchronous write requests (the requests are preferably issued sequentially, but the actual writing of the data from the log buffer to permanent storage will occur asynchronously) for each of the chunks. The chunk size should preferably be chosen for good balance for both efficient transference of data to storage (ie. system throughput) and application response time. Writing chunks asynchronously gives the logger an advantage since it can perform other work while the log records are being transferred to permanent storage.
Under an aspect of the invention, the latch is required only for each database connection agent to make its reservation in the log buffer. The serialization of the latch results in the sequential order of these reservations in the log buffer, which can be used to sequentially order the log records to write them to the permanent storage. The writing of the log data to its reservation by one database connection agent is done independently of any other database connection agent making a reservation. Multiple database connection agents independently write log data to their respective reservations. The tracking of the completion of the transference of data into the log buffer is performed by chunk counters.